{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymssql\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.types import VARCHAR, Float, Integer, Date, Numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "连接成功!\n"
     ]
    }
   ],
   "source": [
    "\n",
    "connect = pymssql.connect('192.168.3.112', 'sa', 'taotao778899!', 'DB_INFO', charset='utf8')  #建立连接\n",
    "if connect:\n",
    "    print(\"连接成功!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>INDEXCODE</th>\n",
       "      <th>TRADINGCODE</th>\n",
       "      <th>UPDATETIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00003</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00011</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00027</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ID INDEXCODE TRADINGCODE UPDATETIME\n",
       "0  2009       HSI       00003       None\n",
       "1  2010       HSI       00011       None\n",
       "2  2011       HSI       00027       None"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"select* from INX_COMPONENT  where indexcode = 'HSI'\"\n",
    "df0 = pd.read_sql(sql,connect)\n",
    "df=pd.DataFrame(df0)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>INDEXCODE</th>\n",
       "      <th>TRADINGCODE</th>\n",
       "      <th>UPDATETIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00003</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00011</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00027</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00005</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2013</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00006</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2014</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00012</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2015</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00017</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00001</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2017</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00019</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00066</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2019</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00002</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2020</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00016</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2021</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00151</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00175</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2023</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00386</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2024</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00288</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2025</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00762</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2026</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00700</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2027</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00857</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2028</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00688</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2029</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00101</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2030</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00823</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2031</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00083</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2032</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00388</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2033</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00669</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2034</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00267</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2035</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01038</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2036</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00939</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2037</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01044</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2038</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01109</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2039</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01093</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2040</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01113</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2041</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01088</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2042</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01997</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2043</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00941</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2044</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01398</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2045</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02007</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2046</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01299</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2047</td>\n",
       "      <td>HSI</td>\n",
       "      <td>00883</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2048</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01177</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2049</td>\n",
       "      <td>HSI</td>\n",
       "      <td>01928</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2050</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02313</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>2051</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02388</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2052</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02628</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>2053</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02382</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>2054</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02319</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>2055</td>\n",
       "      <td>HSI</td>\n",
       "      <td>03988</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>2056</td>\n",
       "      <td>HSI</td>\n",
       "      <td>03328</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2057</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02018</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>2058</td>\n",
       "      <td>HSI</td>\n",
       "      <td>02318</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID INDEXCODE TRADINGCODE UPDATETIME\n",
       "0   2009       HSI       00003       None\n",
       "1   2010       HSI       00011       None\n",
       "2   2011       HSI       00027       None\n",
       "3   2012       HSI       00005       None\n",
       "4   2013       HSI       00006       None\n",
       "5   2014       HSI       00012       None\n",
       "6   2015       HSI       00017       None\n",
       "7   2016       HSI       00001       None\n",
       "8   2017       HSI       00019       None\n",
       "9   2018       HSI       00066       None\n",
       "10  2019       HSI       00002       None\n",
       "11  2020       HSI       00016       None\n",
       "12  2021       HSI       00151       None\n",
       "13  2022       HSI       00175       None\n",
       "14  2023       HSI       00386       None\n",
       "15  2024       HSI       00288       None\n",
       "16  2025       HSI       00762       None\n",
       "17  2026       HSI       00700       None\n",
       "18  2027       HSI       00857       None\n",
       "19  2028       HSI       00688       None\n",
       "20  2029       HSI       00101       None\n",
       "21  2030       HSI       00823       None\n",
       "22  2031       HSI       00083       None\n",
       "23  2032       HSI       00388       None\n",
       "24  2033       HSI       00669       None\n",
       "25  2034       HSI       00267       None\n",
       "26  2035       HSI       01038       None\n",
       "27  2036       HSI       00939       None\n",
       "28  2037       HSI       01044       None\n",
       "29  2038       HSI       01109       None\n",
       "30  2039       HSI       01093       None\n",
       "31  2040       HSI       01113       None\n",
       "32  2041       HSI       01088       None\n",
       "33  2042       HSI       01997       None\n",
       "34  2043       HSI       00941       None\n",
       "35  2044       HSI       01398       None\n",
       "36  2045       HSI       02007       None\n",
       "37  2046       HSI       01299       None\n",
       "38  2047       HSI       00883       None\n",
       "39  2048       HSI       01177       None\n",
       "40  2049       HSI       01928       None\n",
       "41  2050       HSI       02313       None\n",
       "42  2051       HSI       02388       None\n",
       "43  2052       HSI       02628       None\n",
       "44  2053       HSI       02382       None\n",
       "45  2054       HSI       02319       None\n",
       "46  2055       HSI       03988       None\n",
       "47  2056       HSI       03328       None\n",
       "48  2057       HSI       02018       None\n",
       "49  2058       HSI       02318       None"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
